IF OBJECT_ID('vw_paymentrequest') is not null
 DROP VIEW vw_paymentrequest;
GO

CREATE VIEW [vw_paymentrequest] AS 
SELECT lineitem.projectid AS projectid, 
lineitem.paymentrequestid AS paymentrequestid, 
ISNULL(SUM(lineitem.paymentrequestamount), 0) AS totalamount
FROM paymentrequestlineitem lineitem
LEFT JOIN paymentrequest request ON request.id = lineitem.paymentrequestid
GROUP BY lineitem.projectid, lineitem.paymentrequestid
GO

IF OBJECT_ID('vw_projectpayment') is not null
 DROP VIEW vw_projectpayment;
GO

CREATE VIEW [vw_projectpayment] AS 
SELECT 
project.id, 
project.projectid AS projectid, 
project.projecttitle AS title, 
project.costcenternumber AS costcenternumber, 
projecttype.projecttype AS projecttypedesc, 
projectstatus.statusname AS status, 
region.regionname AS region, 
country.countryname AS country, 
project.acronym AS acronym, 
project.projectstartdate AS projectstartdate, 
project.projectenddate AS projectenddate, 
partner.partnername AS partnername, 
project.budgetoverride AS budgetoverride, 
project.statusoverride AS statusoverride, 
(ISNULL(project.managementfunds,0) + ISNULL(project.partnergrantfunds, 0)) AS totalfunds, 
((ISNULL(project.managementfunds,0) + ISNULL(project.partnergrantfunds, 0)) - 
	ISNULL((SELECT SUM(paymentrequestamount) FROM paymentrequestlineitem payment WHERE payment.projectid = project.id), 0)) AS remainingfunds 
FROM project project 
LEFT JOIN projecttype projecttype ON projecttype.id = project.projecttypeid 
LEFT JOIN projectstatus projectstatus ON projectstatus.id = project.projectstatusid 
LEFT JOIN region region ON region.id = project.regionid 
LEFT JOIN country country ON country.id = project.countryid 
LEFT JOIN partner partner ON partner.id = project.partnerid

GO